/**
 * Project: ld.biz.web
 * 
 * File Created at 2012-9-23
 * $Id$
 * 
 * Copyright 1999-2100 Alibaba.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.liangdev.web.action.wiki;

import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.annotation.Resource;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.liangdev.biz.core.autocode.DBMetaDataManager;
import com.liangdev.biz.dao.common.DbDataQueryDao;
import com.liangdev.biz.dataobject.autocode.DbInfoDO;
import com.liangdev.biz.entity.autocode.FieldMetaData;
import com.liangdev.biz.entity.autocode.TableMetaData;
import com.liangdev.biz.service.autocode.DbInfoService;
import com.liangdev.biz.web.util.ActionUtil;
import com.liangdev.common.util.StringUtil;
import com.liangdev.web.vo.CommonJsonVO;
import com.liangdev.web.vo.Message;
import com.opensymphony.xwork2.ActionSupport;

/**
 * 获取数据库配置信息
 * 
 * @author chongan.wangca
 */
public class DatabaseInfoAction extends ActionSupport {

    private static final long serialVersionUID = -5147166361020554487L;

    private final Logger      logger           = LoggerFactory.getLogger(DatabaseInfoAction.class);

    @Resource
    private ActionUtil        actionUtil;

    @Resource
    private DbInfoService     dbInfoService;

    @Resource
    private DBMetaDataManager dbMetaDataManager;

    @Resource
    private DbDataQueryDao    dbDataQueryDao;

    public void getDatabaseInfoJson() {
        List<DbInfoDO> dbInfoDOList = dbInfoService.findAll();
        try {
            String json = makeDatabaseInfoJson(dbInfoDOList);
            actionUtil.getResponse().getWriter().print(json);
        } catch (IOException e) {
            logger.error("", e);
        }
    }

    /**
     * 获取表信息
     */
    public void getTableInfoJson() {

        Long dbId = actionUtil.getRequestParamLong("dbId");

        if (dbId == null) {
            return;
        }

        try {
            List<TableMetaData> tableMetaDataList = dbMetaDataManager.getAllTables(dbId);
            String json = makeTableInfoJson(tableMetaDataList);
            System.out.println(json);
            actionUtil.getResponse().getWriter().print(json);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

    }

    //~~~~~~~~~~~~~~~~~~~~~查询SQL~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    /**
     * 执行查询SQL语句
     * 
     * @return
     */
    public void executeSqlJson() {
        //{'data':[{'统计':'销售收入','安徽':823731.0,'福建':1136375.0,'湖南':33700.0,'浙江':32760.0},{'统计':'销售数量','安徽':15218.0,'福建':21375.0,'湖南':600.0,'浙江':546.0}],'columModle':[{'header':'统计','dataIndex':'统计','width':80,'editor':{}},{'header':'安徽','dataIndex':'安徽','editor':{}},{'header':'福建','dataIndex':'福建','editor':{}},{'header':'湖南','dataIndex':'湖南','editor':{}},{'header':'浙江','dataIndex':'浙江','editor':{}}],'fieldsNames':[{name:'统计'},{name:'安徽'},{name:'福建'},{name:'湖南'},{name:'浙江'}]}
        Long dbInfoId = actionUtil.getRequestParamLong("dbInfoId");
        String sql = actionUtil.getRequestParamString("sql");

        if (sql.endsWith(";") || sql.endsWith("；")) {
            sql = StringUtil.delLastChar(sql);
        }

        String checkSql = StringUtil.blankMany2One(sql);

        if (checkSql.toLowerCase().indexOf("drop table") != -1
                || checkSql.toLowerCase().indexOf("delete from") != -1
                || sql.trim().toLowerCase().startsWith("update")
                || sql.toLowerCase().indexOf("select") == -1) {
            Message message = new Message();
            message.setErrorFlag(true);
            message.setMessage("非法的SQL语句，只允许SELECT语句");
            JSONObject jsonObject = JSONObject.fromObject(message);
            String json = jsonObject.toString();
            try {
                actionUtil.getResponse().getWriter().print(json);
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            return;
        }

        List<List<FieldMetaData>> fieldMetaDatasList = null;

        try {
            fieldMetaDatasList = dbDataQueryDao.queryData(dbInfoId, "WL_TRADE", sql);
        } catch (Exception e) {
            e.printStackTrace();
            Message message = new Message();
            message.setErrorFlag(true);
            message.setMessage(e.getMessage());
            JSONObject jsonObject = JSONObject.fromObject(message);
            String json = jsonObject.toString();
            System.out.println(json);
            try {
                actionUtil.getResponse().getWriter().print(json);
            } catch (IOException e1) {
                e1.printStackTrace();
            }
            return;
        }

        List<Map<String, String>> datasList = makeDatas(fieldMetaDatasList);

        String dataJson = makeDataJson(datasList);
        String columModleJson = makeColumModleJson(datasList);
        String fieldsNamesJson = makeFieldsNamesJson(datasList);
        String fieldsTypeJson = makeFieldsTypeJson(fieldMetaDatasList);

        System.out.println(sql);
        StringBuffer json = new StringBuffer();
        json.append("{");
        json.append("'tableName':'" + StringUtil.getTableNameFromSql(sql) + "',");
        json.append(dataJson);
        json.append(",");
        json.append(columModleJson);
        json.append(",");
        json.append(fieldsNamesJson);
        json.append(",");
        json.append(fieldsTypeJson);
        json.append("}");
        System.out.println(json.toString());
        //        System.out.println("---------");
        try {
            actionUtil.getResponse().getWriter().print(json);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return;
    }

    /**
     * 构造查询得到的数据为JSON格式 <br/>
     * 'data': [ { '统计': '销售收入', '安徽': 823731.0, '福建': 1136375.0, '湖南': 33700.0,
     * '浙江': 32760.0 }, { '统计': '销售数量', '安徽': 15218.0, '福建': 21375.0, '湖南':
     * 600.0, '浙江': 546.0 } ]
     * 
     * @param datasList
     * @return
     */
    private String makeDataJson(List<Map<String, String>> datasList) {
        List<Map<String, String>> newDatasList = new ArrayList<Map<String, String>>(datasList);
        //删除掉首个记录，因为这个是表结构信息，不是表记录
        newDatasList.remove(0);
        JSONArray jsonArray = JSONArray.fromObject(newDatasList);
        return "'data':" + jsonArray.toString();
        //        StringBuffer json = new StringBuffer();
        //        json.append("'data':[");
        //        for (Map<String, String> colDataMap : datasList) {
        //            Set<Entry<String, String>> entrySet = colDataMap.entrySet();
        //            json.append("{");
        //            for (Entry<String, String> entry : entrySet) {
        //                String fieldName = entry.getKey();
        //                String fieldValue = entry.getValue();
        //                json.append("'");
        //                json.append(fieldName);
        //                json.append("':");
        //                json.append("'");
        //                json.append(fieldValue);
        //                json.append("'");
        //                json.append(","); //多余的,最后会删除
        //            }
        //            json = StringUtil.delLastChar(json);
        //            json.append("},"); //多余的,最后会删除
        //        }
        //        json = StringUtil.delLastChar(json);
        //        json.append("]");
        //        return json.toString();
    }

    /**
     * 构造columModleJson <br/>
     * 'columModle': [ { 'header': '统计', 'dataIndex': '统计', 'width': 80 }, {
     * 'header': '安徽', 'dataIndex': '安徽' }, { 'header': '福建', 'dataIndex': '福建'
     * }, { 'header': '湖南', 'dataIndex': '湖南' }, { 'header': '浙江', 'dataIndex':
     * '浙江' } ]
     * 
     * @param datasList
     * @return
     */
    private String makeColumModleJson(List<Map<String, String>> datasList) {
        StringBuffer json = new StringBuffer();
        json.append("'columModle':[Ext.create('Ext.grid.RowNumberer',{text : '序号', width : 35}),");
        Set<Entry<String, String>> entrySet = datasList.get(0).entrySet();

        for (Entry<String, String> entry : entrySet) {
            json.append("{");
            String fieldName = entry.getKey();
            json.append("'header':");
            json.append("'");
            json.append(fieldName);
            json.append("',");
            json.append("'dataIndex':");
            json.append("'");
            json.append(fieldName);
            json.append("'");

            int columnWidth = getColumnWidth(datasList, fieldName);
            json.append(",width:");
            json.append(columnWidth);

            //json.append(",renderer: function(value, meta, record) { meta.attr = 'style=\"white-space:normal;\"';return value;}");
            json.append(",'editor':{}");
            json.append("},");
        }

        json = StringUtil.delLastChar(json);
        json.append("]");

        return json.toString();
    }

    /**
     * 构造fieldsNames <br/>
     * 'fieldsNames': [ { name: '统计' }, { name: '安徽' }, { name: '福建' }, { name:
     * '湖南' }, { name: '浙江' } ]
     * 
     * @param datasList
     * @return
     */
    private String makeFieldsNamesJson(List<Map<String, String>> datasList) {
        StringBuffer json = new StringBuffer();
        json.append("'fieldsNames':[");
        Set<Entry<String, String>> entrySet = datasList.get(0).entrySet();
        for (Entry<String, String> entry : entrySet) {
            json.append("{");
            String fieldName = entry.getKey();
            json.append("name:");
            json.append("'");
            json.append(fieldName);
            json.append("'");
            json.append("},");

        }
        json = StringUtil.delLastChar(json);
        json.append("]");
        return json.toString();
    }

    private String makeFieldsTypeJson(List<List<FieldMetaData>> fieldMetaDatasList) {
        StringBuffer json = new StringBuffer();
        json.append("'fieldsType':[");

        for (FieldMetaData fieldMetaData : fieldMetaDatasList.get(0)) {
            json.append("'");
            json.append(fieldMetaData.getDbFieldNameUC() + "," + fieldMetaData.getFieldType());
            json.append("',");
        }
        json = StringUtil.delLastChar(json);
        json.append("]");
        return json.toString();
    }

    //~~~~~~~~~~~~~~~~~~~~~查询SQL~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    //    /**
    //     * 执行查询SQL语句
    //     * 
    //     * @return
    //     */
    //    public void executeSqlJson() {
    //        String gridJson = "{'data':[{'统计':'销售收入','安徽':823731.0,'福建':1136375.0,'湖南':33700.0,'浙江':32760.0},{'统计':'销售数量','安徽':15218.0,'福建':21375.0,'湖南':600.0,'浙江':546.0}],'columModle':[{'header':'统计','dataIndex':'统计','width':80,'editor':{}},{'header':'安徽','dataIndex':'安徽','editor':{}},{'header':'福建','dataIndex':'福建','editor':{}},{'header':'湖南','dataIndex':'湖南','editor':{}},{'header':'浙江','dataIndex':'浙江','editor':{}}],'fieldsNames':[{name:'统计'},{name:'安徽'},{name:'福建'},{name:'湖南'},{name:'浙江'}]}";
    //
    //        try {
    //            actionUtil.getResponse().getWriter().print(gridJson);
    //        } catch (IOException e) {
    //            e.printStackTrace();
    //        }
    //        return;
    //    }

    private List<Map<String, String>> makeDatas(List<List<FieldMetaData>> fieldMetaDatasList) {
        List<Map<String, String>> datasMap = new ArrayList<Map<String, String>>();
        for (List<FieldMetaData> fieldMetaDatas : fieldMetaDatasList) {
            Map<String, String> map = new LinkedHashMap<String, String>();
            for (FieldMetaData fieldMetaData : fieldMetaDatas) {
                map.put(fieldMetaData.getDbFieldNameUC(), fieldMetaData.getValueStr());
            }
            datasMap.add(map);
        }
        return datasMap;
    }

    /**
     * 计算列宽度，自适应宽度
     * 
     * @param datasList
     * @param fieldName
     * @return
     */
    private int getColumnWidth(List<Map<String, String>> datasList, String fieldName) {

        //该列的字段最长的字数
        int maxLength = 0;
        for (Map<String, String> dataMap : datasList) {
            String value = dataMap.get(fieldName);
            int valueLength = StringUtil.statWordsCount(value);
            int fieldNameLength = StringUtil.statWordsCount(fieldName);
            if (value != null && (valueLength > maxLength || fieldNameLength > maxLength)) {
                maxLength = Math.max(valueLength, fieldNameLength);
            }
        }

        if (maxLength < 12) {
            return 100;
        }
        //        if (maxLength > 50) {
        //            return 400;
        //        }

        int size = 100 + (maxLength - 12) * 7;
        return size > 600 ? 600 : size;
    }

    /**
     * 转化为JSON
     * 
     * @param dbInfoDOList
     * @return
     */
    private String makeDatabaseInfoJson(List<DbInfoDO> dbInfoDOList) {
        if (dbInfoDOList == null || dbInfoDOList.size() == 0) {
            return null;
        }

        List<CommonJsonVO> CommonJsonVOList = new ArrayList<CommonJsonVO>();
        for (DbInfoDO dbInfoDO : dbInfoDOList) {
            CommonJsonVO commonJsonVO = new CommonJsonVO();
            commonJsonVO.setId(dbInfoDO.getId() + "");
            commonJsonVO.setName(dbInfoDO.getDbNameCn() + "(" + dbInfoDO.getDbName() + ")");
            CommonJsonVOList.add(commonJsonVO);
        }
        JSONArray ja = JSONArray.fromObject(CommonJsonVOList);
        return ja.toString();
    }

    /**
     * 转化为JSON
     * 
     * @param dbInfoDOList
     * @return
     */
    private String makeTableInfoJson(List<TableMetaData> tableMetaDataList) {
        if (tableMetaDataList == null || tableMetaDataList.size() == 0) {
            return null;
        }

        List<CommonJsonVO> CommonJsonVOList = new ArrayList<CommonJsonVO>();
        for (TableMetaData tableMetaData : tableMetaDataList) {
            CommonJsonVO commonJsonVO = new CommonJsonVO();
            commonJsonVO.setId(tableMetaData.getDbTableName());
            commonJsonVO.setName(tableMetaData.getDbTableName());
            CommonJsonVOList.add(commonJsonVO);
        }
        JSONArray ja = JSONArray.fromObject(CommonJsonVOList);
        return ja.toString();
    }

}
